package com.kpmg.datalake.autopackage.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;
import com.alibaba.druid.util.StringUtils;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.kpmg.datalake.common.utils.signutils.Md5Util;
import com.kpmg.datalake.db.model.SysUsr;


public class ExcelOperateUtil {
  private static final Logger LOG = LoggerFactory.getLogger(ExcelOperateUtil.class);

  private ExcelOperateUtil() {}

  private static final String UPLOAD = "upload";
  private static final String DESCRIPTION = "Description";
  private static final String SHEET_NAME = "sheetName";
  private static final String SHEET_DESCRIPTION = "sheetDescript";
  private static final String DATE_FORMAT = "yyyyMMddHHmmss";



  /**
   * @Title: createCustomTemplate
   * @Description: 生成PKG自定义模板
   * @param sourceBook 全量模板对象
   * @param target 生成模板路径
   * @param select 已选择的sheet页名称集合
   * @param uSelect 未选择的sheet页名称集合
   * @return
   * @author changpengye
   * @version V1.0
   * @date 2018年7月13日 上午9:08:27
   * @modifier
   * @reason
   */
  public static boolean createCustomTemplate(Workbook sourceBook, String target, String[] select,
      String[] uSelect) {
    boolean b = sourceBook == null || target == null || target.length() == 0;
    if (b) {
      return false;
    }
    try (OutputStream out = new FileOutputStream(new File(target))) {
      if (uSelect != null && uSelect.length > 0) {
        // 移除未选择的sheet页
        for (String sheetNm : uSelect) {
          int sheetIndex = sourceBook.getSheetIndex(sheetNm);
          sourceBook.removeSheetAt(sheetIndex);
        }
        // 移除sheet页中无效的引用
        int sheetNum = sourceBook.getNumberOfSheets();
        for (int i = 0; i < sheetNum; i++) {
          Sheet sheet = sourceBook.getSheetAt(i);
          clearnSheet(sheet, uSelect);
        }
      }
      // 对sheet进行排序
      if (select != null && select.length > 0) {
        for (int i = 0; i < select.length; i++) {
          sourceBook.setSheetOrder(select[i], i);
        }
      }
      sourceBook.write(out);
      return true;
    } catch (IOException e) {
      LOG.error(e.getMessage());
    }
    return false;
  }

  /**
   * @Title: clearnSheet
   * @Description: 清除当前sheet页失效公式
   * @param sheet
   * @author changpengye
   * @version V1.0
   * @date 2018年7月9日 下午5:59:24
   * @modifier
   * @reason
   */
  private static void clearnSheet(Sheet sheet, String[] uSelect) {
    if (sheet == null) {
      return;
    }
    int rows = sheet.getLastRowNum();
    for (int i = 0; i < rows; i++) {
      Row row = sheet.getRow(i);
      clearnRow(row, uSelect);
    }
  }

  /**
   * @Title: clearnRow
   * @Description: 清除当前所选行中的失效公式
   * @param row
   * @author changpengye
   * @version V1.0
   * @date 2018年7月9日 下午6:00:01
   * @modifier
   * @reason
   */
  private static void clearnRow(Row row, String[] uSelect) {
    if (row == null) {
      return;
    }
    int columns = row.getLastCellNum();
    for (int i = 0; i < columns; i++) {
      Cell cell = row.getCell(i);
      if (cell == null) {
        continue;
      }
      int type = cell.getCellType();
      if (type == Cell.CELL_TYPE_FORMULA) {
        clearFailure(cell, uSelect);
      }
    }
  }

  /**
   * @Title: clearFailure
   * @Description: 清除当前单元格中失效公式
   * @param cell
   * @author changpengye
   * @version V1.0
   * @date 2018年7月9日 下午6:00:31
   * @modifier
   * @reason
   */
  private static void clearFailure(Cell cell, String[] uSelect) {
    // 获取公式
    String formula = cell.getCellFormula();
    // 如果是2003版本，失效引用会变成#REF
    if (formula.toUpperCase().contains("#REF")) {
      cell.setCellType(Cell.CELL_TYPE_BLANK);
      return;
    }
    // 如果是2007版本，则失效引用没有提示
    // 则需要判断是否引用了未选择的sheet页
    for (String item : uSelect) {
      boolean b = formula.contains(item);
      if (b) {
        cell.setCellType(Cell.CELL_TYPE_BLANK);
        return;
      }
    }
  }

  /**
   * @Title: getNewFilePath
   * @Description: 生成项目文件管理路径
   * @param projectId
   * @return
   * @author changpengye
   * @version V1.0
   * @date 2018年7月10日 上午9:26:01
   * @modifier
   * @reason
   */
  public static String getNewFilePath(String projectId) {
    String basePath = System.getProperty("user.home") + File.separator;
    // projectId为敏感数据，所以加密掩饰一下
    basePath += UPLOAD + File.separator + Md5Util.md5Encode(projectId, "UTF-8") + File.separator;
    File dir = new File(basePath);
    if (!dir.exists()) {
      dir.mkdirs();
    }
    return basePath;
  }

  /**
   * @Title: getSheets
   * @Description: 获取指定文件的sheet名称数组
   * @param workBook 模板对象
   * @return
   * @author changpengye
   * @version V1.0
   * @date 2018年7月13日 上午9:11:48
   * @modifier
   * @reason
   */
  public static String[] getSheets(Workbook workBook) {
    if (workBook == null) {
      return new String[] {};
    }
    int num = workBook.getNumberOfSheets();
    String[] sheets = new String[num];
    for (int i = 0; i < num; i++) {
      sheets[i] = workBook.getSheetAt(i).getSheetName();
    }
    return sheets;
  }


  /**
   * @Title: getWorkbook
   * @Description: 通过path获取模板对象
   * @param path
   * @return
   * @author changpengye
   * @version V1.0
   * @date 2018年7月13日 上午10:50:10
   * @modifier
   * @reason
   */
  public static Workbook getWorkbook(String path) {
    Workbook outBook = null;
    try (InputStream in = new FileInputStream(new File(path))) {
      if (path.toUpperCase().endsWith(".XLSX")) {
        outBook = new XSSFWorkbook(in);
      } else {
        outBook = new HSSFWorkbook(in);
      }
      return outBook;
    } catch (IOException e) {
      LOG.error(e.getMessage());
      return null;
    }
  }


  /**
   * 公共方法，处理文件流
   * 
   * @param bookSetFile
   * @param user
   * @return
   * @throws Exception
   */
  public static String handleFileStream(MultipartFile bookSetFile, String uploadPath, SysUsr user)
      throws IOException {

    // 设置上传账套路径
    String basePath = UPLOAD + File.separator;
    String personalPath = basePath + user.getUsrId() + File.separator;
    String uploadDir = uploadPath + File.separator + personalPath;

    // 创建账套文件路径
    File dir = new File(uploadDir);
    if (!dir.exists()) {
      dir.mkdirs();
    }

    String originalName = bookSetFile.getOriginalFilename();

    String fileName = getFileName(originalName);
    File serverFile = new File(uploadDir + fileName);

    bookSetFile.transferTo(serverFile);

    return serverFile.getAbsolutePath();
  }

  /**
   *
   * @param originalName
   * @return
   */
  public static String getFileName(String originalName) {
    // 这个就是把时间戳经过处理得到期望格式的时间
    Date date = new Date();
    SimpleDateFormat simpleDateFormat = new SimpleDateFormat(DATE_FORMAT);
    String time = simpleDateFormat.format(date.getTime());

    return time + "." + originalName.substring(originalName.lastIndexOf('.') + 1);
  }

  /**
   *
   * @param workbook
   * @return
   */
  public static String getCellName(Workbook workbook){
    String cellStr = "";
    Sheet sheet = workbook.getSheetAt((workbook.getNumberOfSheets() - 1));
    if(workbook.getSheet(DESCRIPTION) != null){
      StringBuilder cell = new StringBuilder();
      int rows = sheet.getLastRowNum();
      //获取每行
      for (int i = 2; i <= rows; i++) {
        Row row = sheet.getRow(i);
        cell.append(row.getCell(2)).append(",");
      }
      cellStr = cell.toString().substring(0, (cell.toString().length() - 1));
    }
    return cellStr;
  }

  /**
   *
   * @param workbook
   * @return
   */
  public static String getCellDesc(Workbook workbook) {
    return getCell(workbook);
  }

  /**
   * @param workbook
   * @return
   */
  public static String getCell(Workbook workbook) {
    String cellStr = "";
    Sheet sheet = workbook.getSheetAt((workbook.getNumberOfSheets() - 1));
    if (workbook.getSheet(DESCRIPTION) != null) {
      StringBuilder cell = new StringBuilder();
      int rows = sheet.getLastRowNum();
      // 获取每行
      for (int i = 2; i <= rows; i++) {
        Row row = sheet.getRow(i);
        cell.append(row.getCell(3)).append(",");
      }
      cellStr = cell.toString().substring(0, (cell.toString().length() - 1));
    }
    return cellStr;
  }

  /**
   *
   * @param sheetNameArr
   * @param sheetDescArr
   * @return
   */
  public static List<Map<String, String>> convertArrayToList(String[] sheetNameArr,
      String[] sheetDescArr) {
    List<Map<String, String>> sheetList = new ArrayList<>();
    Map<String, String> sheetMap;
    String str = "null";
    if (!ArrayUtils.isEmpty(sheetDescArr)) {
      for (int i = 0; i < sheetNameArr.length; i++) {
        sheetMap = new HashMap<>(sheetNameArr.length);
        sheetMap.put(SHEET_NAME, sheetNameArr[i]);
        sheetMap.put(SHEET_DESCRIPTION, sheetDescArr[i].equals(str) ? "" : sheetDescArr[i]);
        sheetList.add(sheetMap);
      }
    } else {
      for (int i = 0; i < sheetNameArr.length; i++) {
        sheetMap = new HashMap<>(sheetNameArr.length);
        sheetMap.put(SHEET_NAME, sheetNameArr[i]);
        sheetMap.put(SHEET_DESCRIPTION, "");
        sheetList.add(sheetMap);
      }
    }
    return sheetList;
  }

  /**
   *
   * @param jsonArray
   * @return
   */
  public static List<Map<String, String>> convertJsonArrayToList(JSONArray jsonArray) {
    List<Map<String, String>> sheetList = new ArrayList<>();
    Map<String, String> sheetMap;
    for (Object aJsonArray : jsonArray) {
      JSONObject jsonObject = (JSONObject) aJsonArray;
      String sheetName = jsonObject.getString(SHEET_NAME);
      String sheetDescript = jsonObject.getString(SHEET_DESCRIPTION);

      sheetMap = new HashMap<>();
      sheetMap.put(SHEET_NAME, sheetName);
      sheetMap.put(SHEET_DESCRIPTION, sheetDescript);

      sheetList.add(sheetMap);
    }

    return sheetList;
  }

  public static List<Map<String, String>> getSheetNameList(Workbook workbook, String[] newArr) {
    List<Map<String, String>> sheetNameList = new ArrayList<>();
    Map<String, String> sheetMap;
    String emptyStr = "null";
    String cellStr;
    String cellSheetName;

    String[] cellSheetNameArr = {};
    String[] cellSheetDescArr = {};
    if (workbook != null) {
      cellStr = ExcelOperateUtil.getCellDesc(workbook);
      cellSheetDescArr = cellStr.split(",");

      if (!StringUtils.isEmpty(cellStr)) {
        cellSheetName = ExcelOperateUtil.getCellName(workbook);
        cellSheetNameArr = cellSheetName.split(",");
      }
    }

    for (int i = 0; i < newArr.length; i++) {
      sheetMap = new HashMap<>(newArr.length);
      if (ArrayUtils.contains(cellSheetNameArr, newArr[i])) {
        String tempStr = cellSheetDescArr[ArrayUtils.indexOf(cellSheetNameArr, newArr[i])];
        sheetMap.put(SHEET_NAME, newArr[i]);
        sheetMap.put(SHEET_DESCRIPTION, tempStr.equals(emptyStr) ? "" : tempStr);
        sheetNameList.add(sheetMap);

      } else {
        sheetMap.put(SHEET_NAME, newArr[i]);
        sheetMap.put(SHEET_DESCRIPTION, "");
        sheetNameList.add(sheetMap);
      }
    }

    return sheetNameList;
  }
}
